/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBHandling;

import Entity.Account;
import java.sql.ResultSet;
import java.util.Vector;

/**
 *
 * @author HuyPV
 */
public class AccountHandling {

    private DBUtil du;

    public AccountHandling() {
        du = new DBUtil();
    }

    public Account getAccount(String username) throws Exception {
        ResultSet rs = du.executeSQL("Select * from Staffs WHERE username='" + username + "'");
        rs.next();
        Account acc = new Account();
        acc.setUserName(rs.getString("username"));
        acc.setPassword(rs.getString("passwd"));
        acc.setFullname(rs.getString("fullname"));
        acc.setPhone(rs.getString("phone"));
        acc.setEmail(rs.getString("email"));
        acc.setAddress(rs.getString("address"));
        acc.setRoleID(rs.getString("roleID"));
        du.closeConnection();
        return acc;
    }

    public Vector<Account> getAccounts() throws Exception {
        ResultSet rs = du.executeSQL("Select * from Staffs");   //get all account in database

        Vector<Account> accounts = new Vector<Account>();
        while (rs.next()) {
            Account acc = new Account();
            acc.setUserName(rs.getString("username"));
            acc.setPassword(rs.getString("passwd"));
            acc.setFullname(rs.getString("fullname"));
            acc.setPhone(rs.getString("phone"));
            acc.setEmail(rs.getString("email"));
            acc.setAddress(rs.getString("address"));
            acc.setRoleID(rs.getString("roleID"));
            accounts.add(acc);
        }
        du.closeConnection();
        return accounts;
    }

    public boolean insertAccount(Account acc) throws Exception {
        try {
            String sql = "{call sp_addAccount" + "(?, ?, ?, ?, ?, ?, ?)}";
            Object[] objects = {acc.getUserName(), acc.getPassword(), acc.getFullname(), acc.getPhone(), acc.getEmail(), acc.getAddress(), acc.getRoleID()};
            return du.executeProcedureUpdate(sql, objects);
        } catch (Exception ex) {
            throw ex;
        }
    }

    public boolean updateAccount(Account acc, String user) throws Exception {
        try {
            String sql = "{call sp_updateAccount" + "(?, ?, ?, ?, ?, ?, ?, ?)}";
            Object[] objects = {user, acc.getUserName(), acc.getFullname(), acc.getPassword(), acc.getPhone(), acc.getEmail(), acc.getAddress(), acc.getRoleID()};

            return du.executeProcedureUpdate(sql, objects);
        } catch (Exception ex) {
            throw ex;
        }
    }

    public boolean updateAccountWithoutPassword(Account acc, String user) throws Exception {
        try {
            String sql = "{call sp_updateAccountWithoutPassword" + "(?, ?, ?, ?, ?, ?, ?)}";
            Object[] objects = {user, acc.getUserName(), acc.getFullname(), acc.getPhone(), acc.getEmail(), acc.getAddress(), acc.getRoleID()};

            return du.executeProcedureUpdate(sql, objects);
        } catch (Exception ex) {
            throw ex;
        }
    }

    public boolean removeAccount(String user) throws Exception {
        try {
            String sql = "{call sp_removeAccount" + "(?)}";
            Object[] objects = {user};

            return du.executeProcedureUpdate(sql, objects);
        } catch (Exception ex) {
            throw ex;
        }
    }

    public Vector<String> getSalesPersonNames() throws Exception {
        try {
            Vector<String> sps = new Vector<String>();
            ResultSet rs = du.executeSQL("Select username from Staffs WHERE RoleID = 'SP'");   //get all salesperson in database
            while (rs.next()) {
                sps.add(rs.getString("username"));
            }
            du.closeConnection();
            return sps;
        } catch (Exception ex) {
            throw ex;
        }
    }
}
